<?php
/*
$Id: stats_recover_cart_sales.php 118 2013-09-21 12:09:28Z phone.mueller@googlemail.com $
Recover Cart Sales Report v2.12 for xt:Commerce

Copyright (c) 2006 Andre Estel www.estelco.de

Recover Cart Sales contribution: JM Ivler 11/20/03
(c) Ivler / Ideas From the Deep / osCommerce

Released under the GNU General Public License

Modifed by Aalst (recover_cart_sales.php,v 1.2 .. 1.36)
aalst@aalst.com
<script language="javascript" src="includes/menu.js"></script>
Modified by Lane Roathe (recover_cart_sales.php,v 1.4d .. v2.11)
lane@ifd.com	www.osc-modsquad.com / www.ifd.com
*/
require('includes/application_top.php');
require(DIR_WS_INCLUDES.'metatag.php');
require(DIR_WS_CLASSES . 'currencies.php');

$currencies = new currencies();

function date_order_stat($raw_date) {
    if ($raw_date == '') return false;
    $year = substr($raw_date, 2, 2);
    $month = (int)substr($raw_date, 4, 2);
    $day = (int)substr($raw_date, 6, 2);
    return date(DATE_FORMAT, mktime(0, 0, 0, $month, $day, $year));
}

function seadate($day) {
    $ts = date("U");
    $rawtime = strtotime("-".$day." days", $ts);
    $ndate = date("Ymd", $rawtime);
    return $ndate;
}
?>
</head>
<body>

<?php require(DIR_WS_INCLUDES . 'header.php'); ?>

<div id="wrapper">
	<table class="outerTable" cellspacing="2" cellpadding="2">
  		<tr>
			<td class="columnLeft2" width="<?php echo BOX_WIDTH; ?>" valign="top">
				<table border="0" width="<?php echo BOX_WIDTH; ?>" cellspacing="1" cellpadding="1" class="columnLeft">
					<?php require(DIR_WS_INCLUDES . 'column_left.php'); ?>
				</table>
			</td>
    		<td class="boxCenter" valign="top">
        		<table border="0" width="100%" cellspacing="0" cellpadding="0">
          			<tr>
          				<td colspan="6">
          					<table border="0" width="100%" cellspacing="0" cellpadding="2">
            					<tr>
              						<td class="pageHeading" align="left"><?php echo HEADING_TITLE; ?></td>
              						<td class="pageHeading" align="right">
		                				<?php  
		                				$tdate = isset($_POST['tdate']) ? $_POST['tdate'] : '';
		                				if ($tdate == '') 
		                					$tdate = RCS_REPORT_DAYS;
		                					
	      	              				$ndate = seadate($tdate); 
	      	              				?>
	         	       					<form method="POST" action="stats_recover_cart_sales.php?<?php echo 'menu=' . $_GET['menu'] ?>">
	            	      					<table align="right" width="100%">
	               	     						<tr class="dataTableContent" align="right">
	                  	    						<td><?php echo DAYS_FIELD_PREFIX; ?><input type="text" size="4" value="<? echo $tdate; ?>" name="tdate"><?php echo DAYS_FIELD_POSTFIX; ?><input type="submit" value="<?php echo DAYS_FIELD_BUTTON; ?>"></td>
	   	                 						</tr>
		                  					</table>
				           				</form>
         	         				</td>
            					</tr>
	           				 </table>
          				</td>
          			</tr>
<?php
// Init vars
$custknt = 0;
$total_recovered = 0;
$custlist = '';

// Query database for abandoned carts within our timeframe
$inrec = $db->db_query("SELECT * from ". TABLE_SCART ." WHERE dateadded >= '".$ndate."' ORDER BY dateadded DESC" );
$rc_cnt = $inrec->_numOfRows;

// Loop though each one and process it
for ($i = 0; $i < $rc_cnt; $i++)
{
    $cid = $inrec->fields['customers_id'];
    // we have to get the customer data in order to better locate matching orders
    $crec = $db->db_query("SELECT c.customers_firstname, c.customers_lastname, c.customers_email_address from ".TABLE_CUSTOMERS." c WHERE c.customers_id ='".$cid."'");

    // Query DB for the FIRST order that matches this customer ID and came after the abandoned cart
    $orders = $db->db_query("SELECT o.orders_id, o.customers_id, o.date_purchased, s.orders_status_name, ot.text as order_total, ot.value from " . TABLE_ORDERS . " o left join " . TABLE_ORDERS_TOTAL . " ot on (o.orders_id = ot.orders_id), " . TABLE_ORDERS_STATUS . " s WHERE (o.customers_id = " . (int)$cid . ' OR o.customers_email_address like "' . $crec['customers_email_address'] .'" OR o.customers_name like "' . $crec['customers_firstname'] . ' ' . $crec['customers_lastname'] . '") and o.orders_status >= ' . RCS_PENDING_SALE_STATUS . ' and s.orders_status_id = o.orders_status and o.date_purchased >= "' . $inrec->fields['dateadded'] . '" and ot.class = "ot_total"');


    // If we got a match, create the table entry to display the information
    if($orders)
    {
        $custknt++;
        $total_recovered += $orders->fields['value'];
        $custknt % 2 ? $class = RCS_REPORT_EVEN_STYLE : $class = RCS_REPORT_ODD_STYLE;
        $custlist .= '<tr class=".$class.">';
        $custlist .= '	<td class=datatablecontent align=right>'.$inrec->fields['scartid'].'</td>"';
        $custlist .= '	<td>&nbsp;</td>';
        $custlist .= '	<td class=datatablecontent align=center>'.date_order_stat($inrec->fields['dateadded']).'</td>';
        $custlist .= '	<td>&nbsp;</td>';
        $custlist .= '	<td class=datatablecontent><a href="' . href_link(FILENAME_CUSTOMERS, 'search=' . $crec['customers_lastname'], 'NONSSL') . '">'.$crec['customers_firstname']." ".$crec['customers_lastname'].'</a></td>';
        $custlist .= '	<td class=datatablecontent>'.date_short($orders->fields['date_purchased']).'</td>';
        $custlist .= '	<td class=datatablecontent align=center>'.$orders->fields['orders_status_name'].'</td>';
        $custlist .= '	<td class=datatablecontent align=right>'.strip_tags($orders->fields['order_total']).'</td>';
        $custlist .= '	<td>&nbsp;</td>';
        $custlist .= '</tr>';
    }
}
?>
			<tr>
				<td height="15" colspan="8"></td>
			</tr>
			<tr>
				<td align=right colspan=3 class=main><b><?php echo TOTAL_RECORDS; ?></b></td>
				<td>&nbsp;</td>
				<td align=left colspan=5 class=main><?php echo $rc_cnt; ?></td>
			</tr>
			<tr>
				<td align=right colspan=3 class=main><b><?php echo TOTAL_SALES; ?></b></td>
				<td>&nbsp;</td>
				<td align=left colspan=5 class=main><?php echo $custknt . TOTAL_SALES_EXPLANATION; ?></td>
			</tr>
			<tr>
				<td height="12" COLSPAN=6></td>
			</tr>
			<tr class="dataTableHeadingRow">	<!-- Header -->
				<td width="7%" class="dataTableHeadingContent" align="right"><?php echo TABLE_HEADING_SCART_ID ?></td>
				<td width="1%" class="dataTableHeadingContent">&nbsp;</td>
				<td width="10%" class="dataTableHeadingContent" align="center"><?php echo TABLE_HEADING_SCART_DATE ?></td>
				<td width="1%" class="dataTableHeadingContent">&nbsp;</td>
				<td width="50%" class="dataTableHeadingContent"><?php echo TABLE_HEADING_CUSTOMER ?></td>
				<td width="10%" class="dataTableHeadingContent"><?php echo TABLE_HEADING_ORDER_DATE ?></td>
				<td width="10%" class="dataTableHeadingContent" align="center"><?php echo TABLE_HEADING_ORDER_STATUS ?></td>
				<td width="10%" class="dataTableHeadingContent" align="right"><?php echo TABLE_HEADING_ORDER_AMOUNT ?></td>
				<td width="1%" class="dataTableHeadingContent">&nbsp;</td>
			</tr>
<?php echo $custlist; ?>
			<tr>
				<td colspan=9 valign="bottom"><hr width="100%" size="1" color="#800000" noshade></td>
			</tr>
			<tr class="main">
			  <td align="right" valign="center" colspan=4 class="main"><b><?php echo TOTAL_RECOVERED ?>&nbsp;</b></font></td>
			  <td align=left colspan=3 class="main"><b><?php echo $rc_cnt ? round(($custknt / $rc_cnt) * 100, 2) : 0 ?>%</b></font></td>
			  <td class="main" align="right"><b><?php echo $currencies->format(round($total_recovered, 2)) ?></b></font></td>
			  <td class="main">&nbsp;</td>
			</tr>
	    </table>
<!-- body_text_eof //-->
	</td>
 </tr>
</table>
</div>
<?php require(DIR_WS_INCLUDES . 'footer.php'); ?>
</body>
</html>
<?php require(DIR_WS_INCLUDES . 'application_bottom.php'); ?>